package com.demo.controller;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.sql.*;

/**
 * @author gy
 * @date 2023/3/16
 */
@RestController
@RequestMapping("/jdbc")
public class JdbcController {

    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/testConn")
    public String testConn(HttpServletResponse response, @RequestParam(required = false) String filesUrl) throws SQLException {

        //1、加载驱动(固定写法)
        String driver="com.mysql.cj.jdbc.Driver";
        try {
            Class.forName(driver);
        }catch(java.lang.ClassNotFoundException e) {//如果找不到这个类，执行下面的异常处理
            System.out.println("驱动程序配置未配置成功!!!");
        }

        //2、用户信息、URL(固定写法)
        String URL="jdbc:mysql://localhost:13306/other?serverTimezone=Asia/Shanghai&useUnicode=true" +
                "&characterEncoding=utf-8&useSSL=false";
        String userName = "other";
        String passWord = "123456";

        //3、连接成功，数据库对象(固定写法)
        //Connection接口代表Java程序和数据库的连接对象，只有获得该连接对象后，才能访问数据库，并操作数据表
        Connection conn=null;
        try {
            conn= DriverManager.getConnection(URL,userName,passWord);//建立和数据库的连接，并返回表示连接的Connection对象
            System.out.println("数据库连接成功!!!");
        }catch(Exception e) {//未连接成功，执行下面的异常处理
            System.out.println(e);
            System.out.println("数据库连接失败!!!");
        }

        //4、执行sql的对象(固定写法)
        Statement statement = conn.createStatement();

        //5、执行sql语句,返回结果集
        String sql="select * from user";//sql查询语句
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name ="+resultSet.getObject("name"));
            System.out.println("============");
//            System.out.println("password="+resultSet.getObject("PASSWORD"));
//            System.out.println("email="+resultSet.getObject("email"));
//            System.out.println("birth="+resultSet.getObject("birthday"));
        }//输出结果

        //6、释放连接(固定写法)
        resultSet.close();
        statement.close();
        conn.close();
        return "success";
    }
}
